package org.minuframework.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.minuframework.exception.ExceptionFailed;
import org.minuframework.file.FileUtils;
import org.minuframework.lang.StringUtils;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

public class ExcelReader {
    
    public static final String XLS_STR  = ".xls";
    public static final String XLSX_STR = ".xlsx";
    
    private Workbook             workbook;
    private Sheet                sheet;
    private Row                  row;
    private Map<Integer, String> titleMap;
    
    public ExcelReader(String pathname) throws ExceptionFailed, IOException {
        this(new File(pathname));
    }
    
    public ExcelReader(File file) throws ExceptionFailed, IOException {
        String suffix = StringUtils.toLowerCase(FileUtils.suffix(file.getName()));
        if (suffix.equals(XLS_STR)) {
            this.workbook = new HSSFWorkbook(new FileInputStream(file));
        } else if (suffix.equals(XLSX_STR)) {
            this.workbook = new XSSFWorkbook(new FileInputStream(file));
        } else {
            throw new ExceptionFailed("Incorrect file format in the file " + file);
        }
    }
    
    public Map<Integer, Map<Integer, String>> load() {
        Map<Integer, Map<Integer, String>> result = Maps.newHashMap();
        int rowNum = getLastRowNum();
        int colNum = getPhysicalNumberOfRows();
        for (int i = 0; i < rowNum; i++) {
            row = getSheet().getRow(i);
            if (row != null) {
                Map<Integer, String> cellValue = Maps.newHashMap();
                int j = 0;
                while (j < colNum) {
                    Cell cell = row.getCell(j);
                    if (cell != null && StringUtils.isNotEmpty(cell.toString())) {
                        if (i == 0) {
                            getTitleMap().put(j, cell.toString());
                        }
                        cellValue.put(j, cell.toString());
                    }
                    j++;
                }
                if (!cellValue.isEmpty()) {
                    result.put(i, cellValue);
                }
            }
        }
        return result;
    }
    
    public List<Map<String, String>> asList(int rowNum) {
        List<Map<String, String>> result = Lists.newArrayList();
        Map<Integer, Map<Integer, String>> excel = load();
        for (Entry<Integer, Map<Integer, String>> entry : excel.entrySet()) {
            if (entry.getKey() > rowNum) {
                Map<String, String> param = Maps.newHashMap();
                for (Entry<Integer, String> entry2 : entry.getValue().entrySet()) {
                    String value = entry2.getValue();
                    if (StringUtils.isNotEmpty(value)) {
                        String key = getTitleMap().get(entry2.getKey());
                        if (StringUtils.isNotEmpty(key)) {
                            param.put(key, value);
                        }
                    }
                }
                if (!param.isEmpty()) {
                    result.add(param);
                }
            } else {
                continue;
            }
        }
        return result;
    }
    
    public Sheet getSheet() {
        return (this.sheet == null) ? (this.sheet = this.workbook.getSheetAt(0)) : this.sheet;
    }
    
    public int getFirstRowNum() {
        return getSheet().getFirstRowNum();
    }
    
    public int getLastRowNum() {
        return getSheet().getLastRowNum();
    }
    
    public int getPhysicalNumberOfRows() {
        return getSheet().getPhysicalNumberOfRows();
    }
    
    public Map<Integer, String> getTitleMap() {
        return titleMap == null ? (titleMap = Maps.newHashMap()) : titleMap;
    }
    
    public void setTitleMap(Map<Integer, String> titleMap) {
        this.titleMap = titleMap;
    }
    
    public static void main(String[] args) {
        String pathname = "C:\\Users\\Administrator\\Documents\\T_Role.xlsx";
        try {
            ExcelReader reader = new ExcelReader(new File(pathname));
            Map<Integer, Map<Integer, String>> result = reader.load();
            for (Entry<Integer, Map<Integer, String>> entry : result.entrySet()) {
                System.out.println(entry);
            }
            List<Map<String, String>> list = reader.asList(1);
            for (Map<String, String> map : list) {
                System.out.println(map);
            }
        } catch (ExceptionFailed e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
}
